﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.OleDb;

namespace Svnhost.Data
{
    /// <summary>
    /// author:magicsky
    /// http://magicsky.cnblogs.com
    /// 2008-09-01
    /// </summary>
    public static class ExcelHelper
    {
        /// <summary>
        /// 获取EXCEL数据
        /// </summary>
        /// <param name="path">文件路径</param>
        /// <param name="hDR">包含表头</param>
        /// <param name="sheetName">表名</param>
        /// <param name="is2007">EXCEL版本</param>
        /// <returns></returns>
        public static DataTable GetData(string path, bool hDR, string sheet,bool is2007)
        {
            string HDR = hDR ? ";HDR=YES" : "";
            string Conn = is2007 ? "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0{1}\""
                                : " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source ={0};Extended Properties=\"Excel 8.0{1}\"";
            string sheetName = "[" + sheet + "$]";
            OleDbConnection myConn = new OleDbConnection(string.Format(Conn, path, HDR));
            string strCom = " SELECT * FROM " + sheetName;
            myConn.Open();
            OleDbDataAdapter myCommand = new OleDbDataAdapter(strCom, myConn);
            DataSet dsData = new DataSet();
            myCommand.Fill(dsData,sheetName);
            myConn.Close();
            if (dsData.Tables.Count > 0)
                return dsData.Tables[0];
            else return new DataTable(sheetName);
        }
    }
}
